<!DOCTYPE html>
<html lang="en">
  <head>
    <title>
        MySQL-1-基础 - rulerLwx Blog
      </title>
        <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <meta name="viewport"
      content="width=device-width, initial-scale=1, maximum-scale=1, minimum-scale=1, user-scalable=no, minimal-ui">
    <meta name="renderer" content="webkit">
    <meta http-equiv="Cache-Control" content="no-transform" />
    <meta http-equiv="Cache-Control" content="no-siteapp" />
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black">
    <meta name="format-detection" content="telephone=no,email=no,adress=no">
    
    <meta name="theme-color" content="#000000" />
    
    <meta http-equiv="window-target" content="_top" />
    
    
    <meta name="description" content="数据类型 学习资料：《MySQL数据库入门》 整数类型 数据类型 字节数 无符号数的取值范围 有符号数的取值范围 TINYINT 1 0 ~ 255 -128 ~ 127 SMALLINT 2 0 ~ 65 535 -32 768 ~ 32 768 MEDIUMINT" />
    <meta name="generator" content="Hugo 0.73.0 with theme pure" />
    <title>MySQL-1-基础 - rulerLwx Blog</title>
    
    
    <link rel="stylesheet" href="https://rulerLwx.gitee.io/css/style.min.c4bc7071f132c964c2116bca53b392933f377e5ca7b7051ed245187c621a2d3e.css">
    
    <link rel="stylesheet" href="https://cdn.staticfile.org/highlight.js/9.15.10/styles/github.min.css" async>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/tocbot/4.4.2/tocbot.css" async>
    <meta property="og:title" content="MySQL-1-基础" />
<meta property="og:description" content="数据类型 学习资料：《MySQL数据库入门》 整数类型 数据类型 字节数 无符号数的取值范围 有符号数的取值范围 TINYINT 1 0 ~ 255 -128 ~ 127 SMALLINT 2 0 ~ 65 535 -32 768 ~ 32 768 MEDIUMINT" />
<meta property="og:type" content="article" />
<meta property="og:url" content="https://rulerLwx.gitee.io/2020/07/mysql-1-%E5%9F%BA%E7%A1%80/" />
<meta property="article:published_time" content="2020-07-01T18:58:47+08:00" />
<meta property="article:modified_time" content="2020-07-01T18:58:47+08:00" />
<meta itemprop="name" content="MySQL-1-基础">
<meta itemprop="description" content="数据类型 学习资料：《MySQL数据库入门》 整数类型 数据类型 字节数 无符号数的取值范围 有符号数的取值范围 TINYINT 1 0 ~ 255 -128 ~ 127 SMALLINT 2 0 ~ 65 535 -32 768 ~ 32 768 MEDIUMINT">
<meta itemprop="datePublished" content="2020-07-01T18:58:47&#43;08:00" />
<meta itemprop="dateModified" content="2020-07-01T18:58:47&#43;08:00" />
<meta itemprop="wordCount" content="3755">



<meta itemprop="keywords" content="" /><meta name="twitter:card" content="summary"/>
<meta name="twitter:title" content="MySQL-1-基础"/>
<meta name="twitter:description" content="数据类型 学习资料：《MySQL数据库入门》 整数类型 数据类型 字节数 无符号数的取值范围 有符号数的取值范围 TINYINT 1 0 ~ 255 -128 ~ 127 SMALLINT 2 0 ~ 65 535 -32 768 ~ 32 768 MEDIUMINT"/>

    <!--[if lte IE 9]>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/classlist/1.1.20170427/classList.min.js"></script>
      <![endif]-->

    <!--[if lt IE 9]>
        <script src="https://cdn.jsdelivr.net/npm/html5shiv@3.7.3/dist/html5shiv.min.js"></script>
        <script src="https://cdn.jsdelivr.net/npm/respond.js@1.4.2/dest/respond.min.js"></script>
      <![endif]-->
  </head>

  
  

  <body class="main-center theme-black" itemscope itemtype="http://schema.org/WebPage"><header class="header" itemscope itemtype="http://schema.org/WPHeader">
    <div class="slimContent">
      <div class="navbar-header">
        <div class="profile-block text-center">
          <a id="avatar" href="https://gitee.com/rulerLwx" target="_blank">
            <img class="img-circle img-rotate" src="https://rulerLwx.gitee.io/avatar.png" width="200" height="200">
          </a>
          <h2 id="name" class="hidden-xs hidden-sm">rulerLwx</h2>
          <h3 id="title" class="hidden-xs hidden-sm hidden-md">thinking...</h3>
          <small id="location" class="text-muted hidden-xs hidden-sm"><i class="icon icon-map-marker"></i>Guangzhou, China</small>
        </div><div class="search" id="search-form-wrap">
    <form class="search-form sidebar-form">
        <div class="input-group">
            <input type="text" class="search-form-input form-control" placeholder="Search" />
            <span class="input-group-btn">
                <button type="submit" class="search-form-submit btn btn-flat" onclick="return false;"><i
                        class="icon icon-search"></i></button>
            </span>
        </div>
        <div class="ins-search">
            <div class="ins-search-mask"></div>
            <div class="ins-search-container">
                <div class="ins-input-wrapper">
                    <input type="text" class="ins-search-input" placeholder="Type something..."
                        x-webkit-speech />
                    <button type="button" class="close ins-close ins-selectable" data-dismiss="modal"
                        aria-label="Close"><span aria-hidden="true">×</span></button>
                </div>
                <div class="ins-section-wrapper">
                    <div class="ins-section-container"></div>
                </div>
            </div>
        </div>
    </form>
</div>
        <button class="navbar-toggle collapsed" type="button" data-toggle="collapse" data-target="#main-navbar" aria-controls="main-navbar" aria-expanded="false">
          <span class="sr-only">Toggle navigation</span>
          <span class="icon-bar"></span>
          <span class="icon-bar"></span>
          <span class="icon-bar"></span>
        </button>
      </div>
      <nav id="main-navbar" class="collapse navbar-collapse" itemscope itemtype="http://schema.org/SiteNavigationElement" role="navigation">
        <ul class="nav navbar-nav main-nav">
            <li class="menu-item menu-item-home">
                <a href="/">
                    <i class="icon icon-home-fill"></i>
                  <span class="menu-title">Home</span>
                </a>
            </li>
            <li class="menu-item menu-item-archives">
                <a href="/posts/">
                    <i class="icon icon-archives-fill"></i>
                  <span class="menu-title">Archives</span>
                </a>
            </li>
            <li class="menu-item menu-item-categories">
                <a href="/categories/">
                    <i class="icon icon-folder"></i>
                  <span class="menu-title">Categories</span>
                </a>
            </li>
            <li class="menu-item menu-item-tags">
                <a href="/tags/">
                    <i class="icon icon-tags"></i>
                  <span class="menu-title">Tags</span>
                </a>
            </li>
            <li class="menu-item menu-item-about">
                <a href="/about/">
                    <i class="icon icon-cup-fill"></i>
                  <span class="menu-title">About</span>
                </a>
            </li>
        </ul>
      </nav>
    </div>
  </header>

<aside class="sidebar" itemscope itemtype="http://schema.org/WPSideBar">
  <div class="slimContent">
    
      <div class="widget">
    <h3 class="widget-title">Board</h3>
    <div class="widget-body">
        <div id="board">
            <div class="content">enjoy~
            </div>
        </div>
    </div>
</div>

      <div class="widget">
    <h3 class="widget-title"> Categories</h3>
    <div class="widget-body">
        <ul class="category-list">
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/java-framework/" class="category-list-link">java-framework</a><span class="category-list-count">38</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/java-front-end/" class="category-list-link">java-front-end</a><span class="category-list-count">11</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/java-se/" class="category-list-link">java-se</a><span class="category-list-count">21</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/java-senior/" class="category-list-link">java-senior</a><span class="category-list-count">4</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/linux/" class="category-list-link">linux</a><span class="category-list-count">13</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/tools/" class="category-list-link">tools</a><span class="category-list-count">1</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/%E6%8A%80%E6%9C%AF%E6%9D%82%E7%83%A9/" class="category-list-link">技术杂烩</a><span class="category-list-count">4</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/" class="category-list-link">数据库</a><span class="category-list-count">15</span></li>
        </ul>
    </div>
</div>
      <div class="widget">
    <h3 class="widget-title"> Tags</h3>
    <div class="widget-body">
        <ul class="tag-list">
            
            
            <li class="tag-list-item"><a href="https://rulerLwx.gitee.io/tags/jvm/" class="tag-list-link">jvm</a><span
                    class="tag-list-count">1</span></li>
            
            
            <li class="tag-list-item"><a href="https://rulerLwx.gitee.io/tags/%E5%A4%9A%E7%BA%BF%E7%A8%8B/" class="tag-list-link">多线程</a><span
                    class="tag-list-count">2</span></li>
            
            
            <li class="tag-list-item"><a href="https://rulerLwx.gitee.io/tags/%E7%BD%91%E7%BB%9C%E7%BC%96%E7%A8%8B/" class="tag-list-link">网络编程</a><span
                    class="tag-list-count">3</span></li>
            
        </ul>

    </div>
</div>
      
<div class="widget">
    <h3 class="widget-title">Recent Posts</h3>
    <div class="widget-body">
        <ul class="recent-post-list list-unstyled no-thumbnail">
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/%E8%87%AA%E5%B7%B1%E5%8A%A8%E6%89%8B%E4%B8%80%E6%89%B9%E9%87%8F%E7%A7%BB%E5%8A%A8%E9%87%8D%E5%91%BD%E5%90%8D%E6%96%87%E4%BB%B6/" class="title">自己动手（一）——批量移动、重命名文件</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-27 17:50:02 &#43;0800 CST" itemprop="datePublished">2020-07-27</time>
                    </p>
                </div>
            </li>
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/%E5%85%B3%E4%BA%8Einteger%E7%9A%84-128~127%E7%BC%93%E5%AD%98/" class="title">关于Integer的 -128~127缓存</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-11 16:56:21 &#43;0800 CST" itemprop="datePublished">2020-07-11</time>
                    </p>
                </div>
            </li>
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/%E8%B7%A8%E5%9F%9F%E9%97%AE%E9%A2%98/" class="title">跨域问题</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-08 22:41:12 &#43;0800 CST" itemprop="datePublished">2020-07-08</time>
                    </p>
                </div>
            </li>
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/%E4%B8%AA%E4%BA%BA%E5%8D%9A%E5%AE%A2%E6%90%AD%E5%BB%BA/" class="title">个人博客搭建</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-05 18:58:47 &#43;0800 CST" itemprop="datePublished">2020-07-05</time>
                    </p>
                </div>
            </li>
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/centos/" class="title">CentOS-</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-01 18:58:47 &#43;0800 CST" itemprop="datePublished">2020-07-01</time>
                    </p>
                </div>
            </li>
        </ul>
    </div>
</div>
  </div>
</aside>

    
    
<aside class="sidebar sidebar-toc collapse" id="collapseToc" itemscope itemtype="http://schema.org/WPSideBar">
  <div class="slimContent">
    <h4 class="toc-title">Catalogue</h4>
    <nav id="toc" class="js-toc toc">

    </nav>
  </div>
</aside>
<main class="main" role="main"><div class="content">
  <article id="-" class="article article-type-" itemscope
    itemtype="http://schema.org/BlogPosting">
    
    <div class="article-header">
      <h1 itemprop="name">
  <a
    class="article-title"
    href="/2020/07/mysql-1-%E5%9F%BA%E7%A1%80/"
    >MySQL-1-基础</a
  >
</h1>

      <div class="article-meta">
        
<span class="article-date">
  <i class="icon icon-calendar-check"></i>&nbsp;
<a href="https://rulerLwx.gitee.io/2020/07/mysql-1-%E5%9F%BA%E7%A1%80/" class="article-date">
  <time datetime="2020-07-01 18:58:47 &#43;0800 CST" itemprop="datePublished">2020-07-01</time>
</a>
</span>
<span class="article-category">
  <i class="icon icon-folder"></i>&nbsp;
  <a class="article-category-link" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/"> 数据库 </a>
</span>

        <span class="post-comment"><i class="icon icon-comment"></i>&nbsp;<a href="/2020/07/mysql-1-%E5%9F%BA%E7%A1%80/#comments"
            class="article-comment-link">Comments</a></span>
		<span class="post-wordcount hidden-xs" itemprop="wordCount">Word Count: 3755words</span>
		<span class="post-readcount hidden-xs" itemprop="timeRequired">Read Count: 8minutes </span>
      </div>
    </div>
    <div class="article-entry marked-body js-toc-content" itemprop="articleBody">
      <h1 id="数据类型">数据类型</h1>
<p>学习资料：《MySQL数据库入门》</p>
<h2 id="整数类型">整数类型</h2>
<table>
<thead>
<tr>
<th>数据类型</th>
<th>字节数</th>
<th>无符号数的取值范围</th>
<th>有符号数的取值范围</th>
</tr>
</thead>
<tbody>
<tr>
<td>TINYINT</td>
<td>1</td>
<td>0 ~ 255</td>
<td>-128 ~ 127</td>
</tr>
<tr>
<td>SMALLINT</td>
<td>2</td>
<td>0 ~ 65 535</td>
<td>-32 768 ~ 32 768</td>
</tr>
<tr>
<td>MEDIUMINT</td>
<td>3</td>
<td>0 ~ 16 777 215</td>
<td>-8 388 608 ~ 8 388 608</td>
</tr>
<tr>
<td>INT</td>
<td>4</td>
<td>0 ~ 4 294 967 295</td>
<td>-2 147 483 648 ~ 2 147 483 648</td>
</tr>
<tr>
<td>BIGINT</td>
<td>8</td>
<td>0 ~ 18 446 744 073 709 551 615</td>
<td></td>
</tr>
</tbody>
</table>
<p>取值范围可以根据字节数来算，如 TINYINT 占1个字节，无符号数最大值就是 2^8-1，即255，有符号取值范围 -2^7 ~ 2^7-1</p>
<h2 id="浮点数定点数类型">浮点数、定点数类型</h2>
<table>
<thead>
<tr>
<th>数据类型</th>
<th>字节数</th>
<th>无符号数的取值范围</th>
<th>有符号数的取值范围</th>
</tr>
</thead>
<tbody>
<tr>
<td>FLOAT</td>
<td>4</td>
<td></td>
<td></td>
</tr>
<tr>
<td>DOUBLE</td>
<td>8</td>
<td></td>
<td></td>
</tr>
<tr>
<td>DECIMAL(M,D)</td>
<td>M+2</td>
<td></td>
<td></td>
</tr>
</tbody>
</table>
<p>DECIMAL(M,D)，M表示数据长度，D表示小数点后的长度，如将 3.1415 插入 DECIMAL(6,2) 后，显示结果为 3.14</p>
<h2 id="日期时间类型">日期、时间类型</h2>
<table>
<thead>
<tr>
<th>数据类型</th>
<th>字节数</th>
<th>取值范围</th>
<th>日期格式</th>
<th>零值</th>
</tr>
</thead>
<tbody>
<tr>
<td>YEAR</td>
<td>1</td>
<td>1901 ~ 2155</td>
<td>YYYY</td>
<td>0000</td>
</tr>
<tr>
<td>DATE</td>
<td>4</td>
<td>1000-01-01 ~ 9999-12-31</td>
<td>YYYY-MM-DD</td>
<td>0000-00-00</td>
</tr>
<tr>
<td>TIME</td>
<td>3</td>
<td>-838:59:59 ~ 838:59:59</td>
<td>HH:MM:SS</td>
<td>00:00:00</td>
</tr>
<tr>
<td>DATETIME</td>
<td>8</td>
<td>1000-01-01 00:00:00 ~ 9999-12-31 00:00:00</td>
<td>YYYY-MM-DD HH:MM:SS</td>
<td>0000-00-00 00:00:00</td>
</tr>
<tr>
<td>TIMESTAMP</td>
<td>4</td>
<td>1970-01-01 00:00:01 ~ 2038-01-19 03:13:07</td>
<td>YYYY-MM-DD HH:MM:SS</td>
<td>0000-00-00 00:00:00</td>
</tr>
</tbody>
</table>
<p>now() 和 current_timestamp 均表示系统当前日期和时间</p>
<pre><code class="language-sql">select now() from dual;
select current_timestamp  from dual;
</code></pre>
<h2 id="字符二进制类型">字符、二进制类型</h2>
<table>
<thead>
<tr>
<th>数据类型</th>
<th>说明</th>
</tr>
</thead>
<tbody>
<tr>
<td>CHAR</td>
<td>固定长度的字符串</td>
</tr>
<tr>
<td>VARCHAR()</td>
<td>可变长度的字符串</td>
</tr>
<tr>
<td>TEXT</td>
<td>大文本数据，如文章内容、评论等</td>
</tr>
<tr>
<td>ENUM()</td>
<td>表示枚举类型，只能存储一个枚举字符串值</td>
</tr>
<tr>
<td>SET()</td>
<td>表示字符串对象，可以有零或多个值</td>
</tr>
<tr>
<td>BINARY</td>
<td>表示固定长度的二进制数据</td>
</tr>
<tr>
<td>VARBINARY()</td>
<td>表示可变长度的二进制数据</td>
</tr>
<tr>
<td>BIT</td>
<td>表示位字段类型，二进制</td>
</tr>
</tbody>
</table>
<p>为更好的理解CHAR与VARCHAR的区别，以 CHAR(4) 和 VARCHAR(4) 对比进行说明</p>
<table>
<thead>
<tr>
<th>插入值</th>
<th>CHAR(4)</th>
<th>存储需求</th>
<th>VARCHAR(4)</th>
<th>存储需求</th>
</tr>
</thead>
<tbody>
<tr>
<td>'&rsquo;</td>
<td>'&rsquo;</td>
<td>4个字节</td>
<td>'&rsquo;</td>
<td>1个字节</td>
</tr>
<tr>
<td>&lsquo;ab&rsquo;</td>
<td>&lsquo;ab&rsquo;</td>
<td>4个字节</td>
<td>&lsquo;ab&rsquo;</td>
<td>3个字节</td>
</tr>
<tr>
<td>&lsquo;abc&rsquo;</td>
<td>&lsquo;abc&rsquo;</td>
<td>4个字节</td>
<td>&lsquo;abc&rsquo;</td>
<td>4个字节</td>
</tr>
<tr>
<td>&lsquo;abcd&rsquo;</td>
<td>&lsquo;abcd&rsquo;</td>
<td>4个字节</td>
<td>&lsquo;abcd&rsquo;</td>
<td>5个字节</td>
</tr>
<tr>
<td>&lsquo;abcdef&rsquo;</td>
<td>&lsquo;abcde&rsquo;</td>
<td>4个字节</td>
<td>&lsquo;abcd&rsquo;</td>
<td>5个字节</td>
</tr>
</tbody>
</table>
<p>当数据为 CHAR(4) 时，不管插入值的长度为多少，所占用存储空间都是4个字节，而 VARCHAR(4) 所对应的数据所占用的字节数为实际长度加1</p>
<p>TEXT类型有以下几种：</p>
<table>
<thead>
<tr>
<th>数据类型</th>
<th>存储范围</th>
</tr>
</thead>
<tbody>
<tr>
<td>TINYTEXT</td>
<td>0 ~ 255 字节</td>
</tr>
<tr>
<td>TEXT</td>
<td>0 ~ 65 535 字节</td>
</tr>
<tr>
<td>MEDIUMTEXT</td>
<td>0 ~ 16 777 215 字节</td>
</tr>
<tr>
<td>LONGTEXT</td>
<td>0 ~ 4 294 967 295 字节</td>
</tr>
</tbody>
</table>
<ul>
<li>共享锁（读锁）、排他锁（写锁）：读锁是共享的，或者说是相互不阻塞的；写锁则是排他，也就是说一个写锁会阻塞其它的写锁和读锁。</li>
</ul>
<h1 id="表的约束">表的约束</h1>
<h2 id="主键约束">主键约束</h2>
<p>1）单字段主键</p>
<p>2）多字段主键</p>
<p>注意：每个数据表中最多能有一个主键约束，定义为 PRIMARY KEY 的字段不能重复、且不能为NULL</p>
<h2 id="非空约束">非空约束</h2>
<p>通过 NOT NULL 定义</p>
<h2 id="唯一约束">唯一约束</h2>
<p>通过 UNIQUE 定义。表中此字段的值不能重复出现</p>
<h2 id="默认约束">默认约束</h2>
<p>通过 DEFAULT 关键字定义。如果没有给这个字段赋值，数据库自动为这个字段插入默认值</p>
<h2 id="外键约束">外键约束</h2>
<p>通过 Foreign Key 定义</p>
<p>如果想删除主表记录时，从表记录也会随之删除，则需要在建立外键约束后添加 <code>on delete cascade</code>或<code>on delete set null</code>，第一条是级联删除，第二条是将参照主表记录的从表记录的外键设为null。如：</p>
<pre><code class="language-sql">create table student_table4
(
    # 为本表建立主键
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher int,
    # 建立外键，级联删除
    foreign key(java_teacher) references teacher_table4(teacher_id) on delete cascade # 也可以用 on delete set null
)
</code></pre>
<h1 id="索引">索引</h1>
<h2 id="索引的分类">索引的分类</h2>
<p>1）普通索引</p>
<p>普通索引是由 KEY 或 INDEX 定义的索引，是MySQL中基本索引类型</p>
<p>2）唯一性索引</p>
<p>唯一性索引是由 UNIQUE 定义的索引</p>
<p>3）全文索引</p>
<p>全文索引是由 FULLTEXT 定义的索引，它只能创建在 CHAR/VARCHAR/TEXT 类型的字段上，现在只有 MyISAM 引擎支持</p>
<p>4）单列索引</p>
<p>5）多列索引</p>
<p>6）空间索引</p>
<p>总结：虽然索引可以提高数据的查询速度，但索引会占用一定的磁盘空间，并且在创建和维护索引时，其消耗的时间是随着数据量的增加而增加的。因此，使用索引时，应该综合考虑索引的优点和缺点。</p>
<h2 id="索引用使用">索引用使用</h2>
<p>可以使用 <code>EXPLAIN</code> 语句查看索引的使用情况</p>
<h1 id="表的操作">表的操作</h1>
<h2 id="创建表">创建表</h2>
<p>方法一：常规姿势</p>
<p>方法二：使用select</p>
<pre><code>---1 mysql 
   create table `新数据表名`
     select  *  from `旧数据表名`;
--------------------------------
---2 oracle
  create table 新数据表名 as select * from 旧数据表名
--------------------------------
--3 mssql 
  select * into [新数据表名] from [旧数据表名]
</code></pre>
<p>1、2已测试——20190814</p>
<p><a href="https://www.cnblogs.com/lairui1232000/p/9206465.html">https://www.cnblogs.com/lairui1232000/p/9206465.html</a></p>
<h1 id="查询">查询</h1>
<h2 id="关系运算符">关系运算符</h2>
<p>1）比较运算符</p>
<table>
<thead>
<tr>
<th>关系运算符</th>
<th>说明</th>
<th>关系运算符</th>
<th>说明</th>
</tr>
</thead>
<tbody>
<tr>
<td>=</td>
<td>等于</td>
<td>&lt;=</td>
<td>小于等于</td>
</tr>
<tr>
<td>&lt;&gt;</td>
<td>不等于</td>
<td>&gt;</td>
<td>大于</td>
</tr>
<tr>
<td>!=</td>
<td>不等于</td>
<td>&gt;=</td>
<td>大于等于</td>
</tr>
<tr>
<td>&lt;</td>
<td>小于</td>
<td></td>
<td></td>
</tr>
</tbody>
</table>
<p>2）between and 包含边界</p>
<p>3）distinct可以作用于单个、多个字段，作用于多个字段的语法：<code>select distinct 字段1,字段2,...</code></p>
<p>作用于多个字段的示例：<code>select distinct gender,name from student</code>，只有gender和name<strong>都相同</strong>才会被认作是重复记录</p>
<p>4）like 的通配符有百分号（%）和下划线（_）</p>
<p><code>%</code>匹配任意长度的字符串，包括空字符串；<code>_</code>匹配单个字符，如果有多个下划线，则下划线之间不能有空格</p>
<p>如果被匹配的字符中有<code>%</code>或<code>_</code>则需要使用<code>\</code>转义，如 <code>like %\%%</code></p>
<p>5）and 和 or</p>
<p>如果 and 和 or 关键字一起使用，<strong>and 的优先级高于 or</strong></p>
<p>6）order by / group by</p>
<p>group by 通常结合聚合函数一样使用，也常常跟 having 一起使用</p>
<p>7）limit {[offset,] row_count}</p>
<p>从第 offset 行开始，返回 row_count 条记录</p>
<h2 id="系统函数">系统函数</h2>
<h3 id="1聚合函数">1）聚合函数：</h3>
<table>
<thead>
<tr>
<th>函数名称</th>
<th>作用</th>
<th>函数名称</th>
<th>作用</th>
</tr>
</thead>
<tbody>
<tr>
<td>count()</td>
<td>返回某列的行数</td>
<td>max()</td>
<td>返回某列的最大值</td>
</tr>
<tr>
<td>sum()</td>
<td>返回某列值的和</td>
<td>min()</td>
<td>返回某列的最小值</td>
</tr>
<tr>
<td>avg()</td>
<td>返回某列的平均值</td>
<td></td>
<td></td>
</tr>
</tbody>
</table>
<h3 id="2数学函数">2）数学函数：</h3>
<table>
<thead>
<tr>
<th>函数名称</th>
<th>作用</th>
<th>函数名称</th>
<th>作用</th>
</tr>
</thead>
<tbody>
<tr>
<td>abs(x)</td>
<td>返回 x 的绝对值</td>
<td>floor(x)</td>
<td>返回不大于 x 的最大整数 <!-- raw HTML omitted --> 举例：<code>floor(8.3)</code>=8，<code>floor(-8.3)</code>=-9</td>
</tr>
<tr>
<td>sqrt(x)</td>
<td>返回 x 的非负2次方根</td>
<td>round(x,y)</td>
<td>对 x 进行四舍五入操作，小数点后保留 y 位 <!-- raw HTML omitted --> 举例：<code>round(5.345,2)</code> = 5.35</td>
</tr>
<tr>
<td>mod(x,y)</td>
<td>返回 x 被 y 除后的余数，相当于 x/y 的余数</td>
<td>truncate(x,y)</td>
<td>舍去 x 中小数点 y 位后面的数，<!-- raw HTML omitted --> 举例：<code>truncate(5.010104,2)</code> = 5.01</td>
</tr>
<tr>
<td>ceiling(x)</td>
<td>返回不小于 x 的最小整数 <!-- raw HTML omitted --> 举例：<code>ceiling(8.3)</code>=9，<code>ceiling(-8.3)</code>=-8</td>
<td>sign(x)</td>
<td>返回 x 的符号，-1、0或1 <!-- raw HTML omitted --> 举例：<code>sign(-7)</code>=-1，<code>sign(7)</code>=1，<code>sign(-0)</code>=0</td>
</tr>
</tbody>
</table>
<h3 id="3字符串函数">3）字符串函数：</h3>
<table>
<thead>
<tr>
<th>函数名称</th>
<th>作用</th>
<th>函数名称</th>
<th>作用</th>
</tr>
</thead>
<tbody>
<tr>
<td>length(str)</td>
<td>返回字符串 str 的长度</td>
<td>substring(str,n.len)</td>
<td>返回字符串 str 的子串，<!-- raw HTML omitted --> 起始位置为n，长度为len <!-- raw HTML omitted --> <code>substring('string',1,3)</code>=<code>str</code></td>
</tr>
<tr>
<td>concat(s1,s2,..)</td>
<td>返回一个或者多个字符串连接产生的新的字符串</td>
<td>reverse(str)</td>
<td>返回字符串反转后的结果</td>
</tr>
<tr>
<td>trim(str)</td>
<td>删除字符串两侧的空格</td>
<td>locate(s1,str)</td>
<td>返回子串 s1 在字符串 str 中的 <!-- raw HTML omitted --> 起始位置</td>
</tr>
<tr>
<td>replace(str,s1,s2)</td>
<td>使用字符串 s2 替换字符串 str 中所有的字符串 s1</td>
<td></td>
<td></td>
</tr>
</tbody>
</table>
<h3 id="4日期和时间函数">4）日期和时间函数</h3>
<table>
<thead>
<tr>
<th>函数名称</th>
<th>作用</th>
<th>函数名称</th>
<th>作用</th>
</tr>
</thead>
<tbody>
<tr>
<td>curdate()</td>
<td>获取系统当前日期，<code>2019-07-16</code></td>
<td>adddate()</td>
<td>执行日期的加运算，<!-- raw HTML omitted --> <code>adddate('2019-02-01',31)</code> = <code>2019-03-04</code></td>
</tr>
<tr>
<td>curtime()</td>
<td>获取系统当前时间，<code>01:19:20</code></td>
<td>subdate()</td>
<td>执行日期的减运算</td>
</tr>
<tr>
<td>sysdate()</td>
<td>获取系统当前日期和时间，<!-- raw HTML omitted --> <code>2019-07-16 01:19:20</code></td>
<td>date_format()</td>
<td>格式化输出日期和时间值</td>
</tr>
<tr>
<td>time_to_sec()</td>
<td>返回将时间转换成秒的结果</td>
<td>datediff()</td>
<td>todo&hellip;</td>
</tr>
</tbody>
</table>
<p>总结：date 的格式就是 yyyy-mm-dd</p>
<h3 id="5条件判断函数">5）条件判断函数：</h3>
<table>
<thead>
<tr>
<th>函数名称</th>
<th>作用</th>
</tr>
</thead>
<tbody>
<tr>
<td>if(expr,v1,v2)</td>
<td>如果 expr 表达式为 true 返回 v1，否则返回 v2</td>
</tr>
<tr>
<td>ifnull(v1,v2)</td>
<td>如果 v1 为 null 返回 v1，否则返回 v2</td>
</tr>
<tr>
<td>case expr when v1 then r1 [when v2 then r2&hellip;] [else rn] end</td>
<td></td>
</tr>
</tbody>
</table>
<h3 id="6加密函数">6）加密函数：</h3>
<table>
<thead>
<tr>
<th>函数名称</th>
<th>作用</th>
</tr>
</thead>
<tbody>
<tr>
<td>md5(str)</td>
<td>对字符串 str 进行 MD5 加密</td>
</tr>
<tr>
<td>encode(str,pwd_str)</td>
<td>使用 pwd 作为密码加密字符串 str</td>
</tr>
<tr>
<td>decode(str,pwd_str)</td>
<td>使用 pwd 作为密码解密字符串 str</td>
</tr>
</tbody>
</table>
<h2 id="连接查询">连接查询</h2>
<h3 id="交叉连接">交叉连接</h3>
<p>语法：select * from 表1 cross join 表2</p>
<p>交叉连接返回的结果是被连接的两个表中所有数据行的笛卡儿积</p>
<p>如 department 表中有4个部门，employee表中有4个员工，那么交叉连接的结果就有 4 * 4 = 16 条数据</p>
<p>实际开发很少用这种连接查询方式！</p>
<h3 id="内连接">内连接</h3>
<p>语法：select 查询字段 from 表1 [inner] join 表2 on 表1.关系字段 = 表2.关系字段  where 条件</p>
<p>这种方式跟使用 where 条件判断效果相同：</p>
<pre><code class="language-sql">select a.* from table a,talbe b where a.id = b.id
</code></pre>
<h3 id="外连接">外连接</h3>
<p>语法：select 所查字段 from  表1 left|right [outer] join 表2 on 表1.关系字段 = 表2.关系字段 where 条件</p>
<p>left/right称为关键字，关键字左边的表称为左表，关键字右边的表称为右表</p>
<p>1）left join ：返回包括左表中的所有记录和右表中符合连接条件的记录，不符合的字段显示为 null</p>
<p>2）right join ：返回包括右表中的所有记录和左表中符合连接条件的记录，不符合的字段显示为 null</p>
<h2 id="子查询">子查询</h2>
<p>常用关键字：in/exists/any/all</p>
<pre><code class="language-sql">select * from department where did &gt; all (select did from employee)
</code></pre>
<h2 id="分页">分页</h2>
<pre><code class="language-sql">select * from test.user limit 0,4 # 记录索引从0开始，表示第一条记录
</code></pre>
<pre><code class="language-sql">select id, name, cert_num as certnum from basic_person_info limit 3 # 第1行开始，3条数据
select id, name, cert_num as certnum from basic_person_info limit 3,3 # 第4行开始，3条数据

# 疑问：
# 为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：   
mysql&gt; SELECT * FROM table LIMIT 95,-1; # 检索记录行 96-last.  

</code></pre>
<p>在Java中第几行是 （当前页码-1）* 每页行数</p>
<pre><code>        //每页记录数，从哪开始
        int limit = 8;
        int offset = (curPage-1) * limit;
        
        //分页得到数据列表
        List&lt;Post&gt; postList = postMapper.listPostByTime(offset,limit);
        
    &lt;select id=&quot;listPostByTime&quot; resultMap=&quot;postMap&quot;&gt;
        select u.uid,u.username,u.head_url,p.pid,p.title,p.publish_time,p.reply_time,p.reply_count,p.like_count,p.scan_count from post p
        join user u on p.uid = u.uid
        order by p.reply_time desc limit #{offset},#{limit}
    &lt;/select&gt;
</code></pre>
<h2 id="分组查询">分组查询</h2>
<pre><code>select sum(grade),gender from student group by gender having sum(grade) &lt; 300;
</code></pre>
<h1 id="事务">事务</h1>
<h2 id="事务的概念">事务的概念</h2>
<p>事务：针对数据库的一组操作，它可以由一条或多条SQL语句组成，这些语句要么都执行，要么都不执行。它必须满足4个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。</p>
<ul>
<li>原子性：一个最小的工作单元</li>
<li>一致性：如唯一性约束</li>
<li>隔离性：多个用户并发访问数据库时，数据库为每个用户开启事务，事务之间的操作数据相互隔离</li>
<li>持久性：事务一旦提交，其所做的修改就会永久保存到数据库中</li>
</ul>
<h2 id="事务的开启提交回滚">事务的开启、提交、回滚</h2>
<pre><code class="language-sql">start transaction;#开启事务

rollback;#回滚事务

commit;#提交事务
</code></pre>
<h2 id="事务的隔离级别">事务的隔离级别</h2>
<p>数据库是多线程并发访问的，所以很容易出现多个线程同时开启事务的情况，容易出现脏读、重复读、幻读</p>
<p>解决方法：为事务设置隔离级别。</p>
<p>MySQL有四种隔离级别：</p>
<table>
<thead>
<tr>
<th>事务级别</th>
<th>说明</th>
</tr>
</thead>
<tbody>
<tr>
<td>read uncommitted</td>
<td>可以读取到另一个事务未提交的数据，也称为脏读（Dirty Read）</td>
</tr>
<tr>
<td>read committed</td>
<td>可以读取其它事务已提交的内容，避免了脏读，避免不了重复读、幻读。<strong>oracle使用此级别</strong></td>
</tr>
<tr>
<td>repeatable read</td>
<td>也称为可重复读，<strong>MySQL默认的事务隔离级别</strong>，可以避免 脏读、重复读、幻读</td>
</tr>
<tr>
<td>serializable</td>
<td>也称可串行化，最高隔离级别，在每个读的数据行上加锁，解决了脏读、重复读、幻读，<!-- raw HTML omitted --> 但可能导致大量超时现象和锁竞争，在实际应用中很少使用</td>
</tr>
</tbody>
</table>
<p>搞清楚两个概念：</p>
<ul>
<li>重复读：在事务内重复读取了别的线程已经提交的数据，但两次读取的结果不一致，原因是查询的过程中其它事务做了更新操作。</li>
<li>幻读：在一个事务内两次查询中数据条数不一致，原因是查询的过程中其它的事务做了添加操作。</li>
</ul>
<p>如何修改事务隔离级别：</p>
<pre><code class="language-sql">set session transaction isolation level read uncommitted;
</code></pre>

    </div>
    <div class="article-footer">
<blockquote class="mt-2x">
  <ul class="post-copyright list-unstyled">
    <li class="post-copyright-link hidden-xs">
      <strong>Permalink: </strong>
      <a href="https://rulerLwx.gitee.io/2020/07/mysql-1-%E5%9F%BA%E7%A1%80/" title="MySQL-1-基础" target="_blank" rel="external">https://rulerLwx.gitee.io/2020/07/mysql-1-%E5%9F%BA%E7%A1%80/</a>
    </li>
    <li class="post-copyright-license">
      <strong>License：</strong><a href="http://creativecommons.org/licenses/by/4.0/deed.zh" target="_blank" rel="external">CC BY 4.0 CN</a>
    </li>
  </ul>
</blockquote>

<div class="panel panel-default panel-badger">
  <div class="panel-body">
    <figure class="media">
      <div class="media-left">
        <a href="https://gitee.com/rulerLwx" target="_blank" class="img-burn thumb-sm visible-lg">
          <img src="https://rulerLwx.gitee.io/avatar.png" class="img-rounded w-full" alt="">
        </a>
      </div>
      <div class="media-body">
        <h3 class="media-heading"><a href="https://gitee.com/rulerLwx" target="_blank"><span class="text-dark">rulerLwx</span><small class="ml-1x">thinking...</small></a></h3>
        <div>Good Good Study, Day Day Up~</div>
      </div>
    </figure>
  </div>
</div>
    </div>
  </article>
<section id="comments">
    <div id="vcomments"></div>
</section>

</div><nav class="bar bar-footer clearfix" data-stick-bottom>
    <div class="bar-inner">
        <ul class="pager pull-left">
            <li class="prev">
                <a href="https://rulerLwx.gitee.io/2020/07/mysql-2-%E5%9F%BA%E7%A1%80/" title="MySQL-2-基础"><i
                        class="icon icon-angle-left"
                        aria-hidden="true"></i><span>&nbsp;&nbsp;Older</span></a>
            </li>
            <li class="next">
                <a href="https://rulerLwx.gitee.io/2020/07/mongodb-2-mongodb-driver/"
                    title="MonggoDB-2"><span>Newer&nbsp;&nbsp;</span><i
                        class="icon icon-angle-right" aria-hidden="true"></i></a>
            </li>
            
            <li class="toggle-toc">
                <a class="toggle-btn collapsed" data-toggle="collapse" href="#collapseToc" aria-expanded="false"
                    title="Catalogue" role="button">
                    <span>[&nbsp;</span><span>Catalogue</span>
                    <i class="text-collapsed icon icon-anchor"></i>
                    <i class="text-in icon icon-close"></i>
                    <span>]</span>
                </a>
            </li>
        </ul>
        <div class="bar-right">
            <div class="share-component" data-sites="weibo,qq,wechat"
                data-mobile-sites="weibo,qq,qzone"></div>
        </div>
    </div>
</nav>

</main><footer class="footer" itemscope itemtype="http://schema.org/WPFooter">
<ul class="social-links">
    <li><a href="https://gitee.com/rulerLwx" target="_blank" title="gitee" data-toggle=tooltip data-placement=top >
            <i class="icon icon-gitee"></i></a></li>
    <li><a href="https://github.com/wolf-lea" target="_blank" title="github" data-toggle=tooltip data-placement=top >
            <i class="icon icon-github"></i></a></li>
</ul>
  <div class="copyright">
    &copy;2020  -
    2020
    <div class="publishby">
        Theme by <a href="https://github.com/xiaoheiAh" target="_blank"> xiaoheiAh </a>base on<a href="https://github.com/xiaoheiAh/hugo-theme-pure" target="_blank"> pure</a>.
    </div>
  </div>
</footer>

<script src="https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.2/MathJax.js?config=TeX-MML-AM_SVG"></script>
<script type="text/x-mathjax-config">
    MathJax.Hub.Config({
            showMathMenu: false, //disables context menu
            tex2jax: {
            inlineMath: [ ['$','$'], ['\\(','\\)'] ]
           }
    });
</script>


<script src="https://cdn.jsdelivr.net/npm/jquery@3.4.1/dist/jquery.min.js"></script>
<script>
    window.jQuery || document.write('<script src="js/jquery.min.js"><\/script>')
</script>
<script type="text/javascript" src="https://cdn.staticfile.org/highlight.js/9.15.10/highlight.min.js"></script>
<script type="text/javascript" src="https://cdn.staticfile.org/highlight.js/9.15.10/languages/python.min.js" defer></script>
<script type="text/javascript" src="https://cdn.staticfile.org/highlight.js/9.15.10/languages/javascript.min.js" defer></script><script>
    hljs.configure({
        tabReplace: '    ', 
        classPrefix: ''     
        
    })
    hljs.initHighlightingOnLoad();
</script>
<script src="https://rulerLwx.gitee.io/js/application.min.bdeb64b910570b6c41badc6a05b7afb0c8ad9efd8525de3c7257d59e786326a3.js"></script>
<script src="https://rulerLwx.gitee.io/js/plugin.min.51ff8c7317566f82259170fa36e09c4493adc9b9378b427a01ad3f017ebac7dd.js"></script>

<script>
    (function (window) {
        var INSIGHT_CONFIG = {
            TRANSLATION: {
                POSTS: 'Posts',
                PAGES: 'Pages',
                CATEGORIES: 'Categories',
                TAGS: 'Tags',
                UNTITLED: '(Untitled)',
            },
            ROOT_URL: 'https:\/\/rulerLwx.gitee.io',
            CONTENT_URL: 'https:\/\/rulerLwx.gitee.io\/searchindex.json ',
        };
        window.INSIGHT_CONFIG = INSIGHT_CONFIG;
    })(window);
</script>
<script type="text/javascript" src="https://rulerLwx.gitee.io/js/insight.min.a343cd9a5a7698336b28ef3a7c16a3a1b1d2d5fb17dc8ed04022bbe08cc5459073a15bdafa3a8a58cdd56080784bdd69fa70b1ae8597565c799c57ed00f0e120.js" defer></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/tocbot/4.4.2/tocbot.min.js"></script>
<script>
    tocbot.init({
        
        tocSelector: '.js-toc',
        
        contentSelector: '.js-toc-content',
        
        headingSelector: 'h1, h2, h3',
        
        hasInnerContainers: true,
    });
</script>

<script src="https://cdn1.lncld.net/static/js/3.0.4/av-min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/valine"></script>
<script type="text/javascript">
    var GUEST = ['nick', 'mail', 'link'];
    var meta = 'nick,mail';
    meta = meta.split(',').filter(function (item) {
        return GUEST.indexOf(item) > -1;
    });
    new Valine({
        el: '#vcomments',
        verify: null ,
        notify: null ,
        appId: 'IyAB0PSPRazTPDxitO1ddQ7O-gzGzoHsz',
        appKey: '5rBJTq4KidYF33eXwvRVhtEH',
        placeholder: 'enjoy~',
        avatar: 'mm',
        meta: meta,
        pageSize: '10' || 10,
        visitor: false 
});
</script>

  </body>
</html>
